package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by feng on 2017/2/24.
 */
public class UserRepositoryImpl {

    @PersistenceContext
    private EntityManager em;

    public UserDto loginCheck(String loginName, String loginPwd) {
        String sql = "{call pro_user_login(?,?)}";
        Query query = em.createNativeQuery(sql).setParameter(1, loginName).setParameter(2, loginPwd);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(UserDto.class));
        return (UserDto) query.getSingleResult();
    }

    public List<UserOrgDto4Notice> getUserNameIdAndOrgIdByActorId(Integer ActorId) throws Exception {
        String sql = "SELECT u.UserId,u.UserName,u.OrgId FROM tb_user AS u , v_user_actor AS ua WHERE ua.ActorId = :ActorId AND u.UserId = ua.UserId";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(UserOrgDto4Notice.class));
        query.setParameter("ActorId", ActorId);
        List<UserOrgDto4Notice> userOrgDto4Notices = query.getResultList();
        return userOrgDto4Notices;
    }

    public List<UserDto> getUser4DT(DTRequestDto dtRequestDto, Integer orgId, Integer actorId) throws Exception {
        String sql = "SELECT UserName,LoginName,UserId,UserSex,UserMobile,UserEmail,UserIcon,UserLoginTime,UserState,u.OrgId,UserAge," +
                "UserProvince,UserCity,UserArea,UserCreateTime,AndroidToken,iosToken,UserLoginCount,UserRemarks,o.orgName FROM tb_user u,tb_org o " +
                "where u.orgId=o.orgId and concat(UserName,LoginName,o.orgName) like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId != 1)
            sql += "and u.orgId=" + orgId + "";
        else
            sql += " and u.orgId!=" + orgId + "";
        sql += " order by userId desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(UserDto.class));
        List<UserDto> userDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return userDtos;
    }

    public Integer getCount4DT(Integer orgId, String strSearch, Integer actorId) throws Exception {
        String sql = "SELECT count(*) FROM tb_user u,tb_org o where u.orgId=o.orgId and " +
                "concat(u.UserName,u.LoginName,o.orgName) like '%" + strSearch + "%' ";
        if (actorId != 1)
            sql += " and u.orgId=" + orgId + "";
        else
            sql += " and u.orgId!=" + orgId + "";
        Query query = em.createNativeQuery(sql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public Integer getCountByLoginName(String loginName) throws Exception {
        String sql = "select userId from tb_user where loginName='" + loginName + "'";
        Query query = em.createNativeQuery(sql);
        List<Integer> user = query.getResultList();
        return user.size();
    }

    public List<UserDto> getLog4DT(DTRequestDto dtRequestDto) throws Exception {
        String sql = "select us.UserName, us.UserState, us.UserLoginTime, us.Ip, og.OrgName " +
                "from tb_user us, tb_org og where og.orgId = us.orgId " +
                "and concat(us.UserName, og.OrgName) like '%" + dtRequestDto.getSearch() + "%' " +
                "order by us.UserLoginTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(UserDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getLogCount4DT(String searchText) throws Exception {
        String sql = "select count(*) from tb_user us, tb_org og where og.orgId = us.orgId " +
                "and concat(us.UserName, og.OrgName) like '%" + searchText + "%'";
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }


    public List<ActorDto> getActorNames4User() throws Exception {
        String sql = "select Group_concat(ActorName) as actorName,userId from v_user_actor GROUP by userId";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ActorDto.class));
        return query.getResultList();
    }

}
